

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.util.JdbcConstants;
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.TStatementList;
import gudusoft.gsqlparser.nodes.TExpression;
import gudusoft.gsqlparser.nodes.TResultColumn;
import gudusoft.gsqlparser.nodes.TResultColumnList;
import gudusoft.gsqlparser.stmt.TCreateTableSqlStatement;
import gudusoft.gsqlparser.stmt.TSelectSqlStatement;

import java.util.LinkedList;
import java.util.stream.StreamSupport;

public class Tgsparser {


    public static void main(String[] args) {
        TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmysql);
//        sqlparser.sqltext = "CREATE TABLE tmp.tmp_a_supp_achievement_an_mom_001 AS\n  SELECT a1.dim_day_txdate,\n         a.a_pin,\n         Sum(Coalesce(b.amount, 0)) AS total_amount\n         , Sum(Coalesce(c.refund_amt, 0)) AS refund_amt\n         , Sum(os_prcp_amt) os_prcp_amt\n  FROM (SELECT dim_day_txdate \n        FROM dmv.dim_day\n        WHERE dim_day_txdate>=concat(cast(Year('2018-05-15')-1 AS string),'-', substring('2018-05-15', 6, 2), '-01')\n          AND dim_day_txdate<='2018-05-15' )a1\n  JOIN (SELECT DISTINCT a_pin, product_type\n        FROM dwd.dwd_as_qy_cust_account_s_d\n        WHERE dt ='2018-05-15' AND product_type='20288' )a\n  LEFT OUTER JOIN (SELECT substring(tx_time, 1, 10) AS time, sum(order_amt) AS amount, a_pin\n                    FROM dwd.dwd_actv_as_qy_iou_receipt_s_d\n                    WHERE a_order_type='20096' AND a_pin NOT IN ('vep_test', 'VOPVSP测试')\n                      AND dt='2018-05-15'\n                    GROUP BY substring(tx_time, 1, 10), a_pin )b\n              ON cast(a.a_pin AS string)=cast(b.a_pin AS string) AND a1.dim_day_txdate=b.time\n  LEFT OUTER JOIN ( SELECT substring(refund_time, 1, 10) AS refund_time, a_pin, sum(refund_amt)AS refund_amt\n                    FROM dwd.dwd_as_qy_iou_refund_s_d\n                    WHERE refund_status='20090' AND dt='2018-05-15' AND a_order_no <> '12467657248'\n                      AND a_refund_no <> '1610230919767139947'\n                    GROUP BY substring(refund_time, 1, 10), a_pin )c\n              ON cast(a.a_pin AS string)=cast(c.a_pin AS string) AND a1.dim_day_txdate=c.refund_time\n  LEFT OUTER JOIN (SELECT dt, a_pin, sum(os_prcp_amt) AS os_prcp_amt\n                    FROM dwd.dwd_as_qy_cycle_detail_s_d\n                    WHERE dt>=concat(substr('2018-05-15', 1, 7), '-01') AND dt<='2018-05-15'\n                    GROUP BY dt, a_pin)e\n              ON cast(a.jd_pin AS string)=cast(e.a_pin AS string) AND a1.dim_day_txdate=e.dt\n  GROUP BY a1.dim_day_txdate, a.a_pin;";
//        sqlparser.sqltext = "SELECT a FROM ee.ff AS f, (SELECT f2.a FROM `schema_bb`.`tbl_bb` as f2, (SELECT a FROM ccc AS c, `dddd`)) ,ab";
        sqlparser.sqltext = "SELECT `user`.name , `order`.price " +
                "               FROM   `user`  " +
                "                left join (select c1 as price from test1) as `order` on user.id=order.id " +
                "            WHERE  `user`.id = `order`.uid";
        System.out.println(SQLUtils.format(sqlparser.sqltext, JdbcConstants.MYSQL));
        int ret = sqlparser.parse();
        if (ret == 0) {
            // 解析出所有语句
            TStatementList stmts = sqlparser.getSqlstatements();

            // 拿到create table语句的实例
            TCreateTableSqlStatement stmt = (TCreateTableSqlStatement) stmts.get(0);

            // 从create table语句的子查询中，拿到select语句的实例，再获取column
            TSelectSqlStatement subquery = stmt.getSubQuery();
            TResultColumnList columns = subquery.getResultColumnList();
            LinkedList<String>[] lineages = new LinkedList[columns.size()];

            for (int i = 0; i < columns.size(); i++) {
                TResultColumn column = columns.getResultColumn(i);
                LinkedList<String> lineage = lineages[i] = new LinkedList<>();
                lineage.addFirst(String.format("%s(%s)", column.getDisplayName(), stmt.getTableName()));
                lineage.addFirst(String.format("%s(RS-1)", column.getDisplayName()));

                String columnName = "";
                if (column.getExpr() != null) {
                    TExpression expr = column.getExpr();
                    while (expr.getFunctionCall() != null) {
                        expr = expr.getFunctionCall().getArgs().getExpression(0);
                    }
                    columnName = expr.toString();
                }

                String[] pair = columnName.split("\\.");
                if (pair.length == 2) {
                    // 有alias，在alias对应的select语句中搜索
                    String prefix = pair[0];
                    String columnDisplayName = pair[1];
                    lineage.addFirst(String.format("%s(%s)", columnDisplayName, prefix));
                    StreamSupport
                            .stream(subquery.tables.spliterator(), false)
                            .filter(t -> t.getAliasClause().toString().equalsIgnoreCase(prefix))
                            .findFirst().ifPresent(table -> {
                                table.subquery.getResultColumnList().forEach(tableColumn -> {
                                    if (tableColumn.getDisplayName().equalsIgnoreCase(columnDisplayName)) {
                                        if (tableColumn.getExpr().getFunctionCall() == null) {
                                            lineage.addFirst(String.format("%s(%s)", columnDisplayName, table.subquery.tables.getTable(0).getTableName()));
                                        } else {
                                            lineage.addFirst(String.format("%s(%s)",
                                                    tableColumn.getExpr().getFunctionCall().getArgs().getElement(0),
                                                    table.subquery.tables.getTable(0).getTableName()));
                                        }
                                    }
                                });
                            });
                } else if (pair.length == 1) {
                    // 没有alias，在所有的select语句中搜索
                    String columnDisplayName = pair[0];
                    StreamSupport
                            .stream(subquery.tables.spliterator(), false)
                            .filter(t -> {
                                for (int j = 0; j < t.subquery.getResultColumnList().size(); j++) {
                                    if (t.subquery.getResultColumnList().getResultColumn(j).getDisplayName().equalsIgnoreCase(columnDisplayName)) {
                                        return true;
                                    }
                                }
                                return false;
                            })
                            .findFirst().ifPresent(table -> {
                                lineage.addFirst(String.format("%s(%s)", columnDisplayName, table.getAliasClause()));
                                lineage.addFirst(String.format("%s(%s)", columnDisplayName, table.subquery.tables.getTable(0)));
                            });
                }

            }

            for (LinkedList<String> lineage : lineages) {
                System.out.println(String.join(" -> ", lineage));
            }

        } else {
            System.out.println(sqlparser.getErrormessage());
        }
    }
}
